package com.zhku.zhny.admin.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.StringUtils;

import com.zhku.zhny.domain.Admin;
import com.zhku.zhny.domain.Event;
import com.zhku.zhny.domain.Group;
import com.zhku.zhny.domain.Honor;
import com.zhku.zhny.domain.Introduction;
import com.zhku.zhny.domain.SignUp;
import com.zhku.zhny.util.C3P0Util;

public class AdminDaoImpl implements AdminDao {
	// 解决数据库字段名到对象属性之间的映射问题
	private BeanProcessor bean = new GenerousBeanProcessor();
	private RowProcessor processor = new BasicRowProcessor(bean);
	
	@Override
	public int insertGroupInfo(Group group) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		int i =qr.update("insert into tbl_group(group_id, group_name, group_introduction, group_img_path) values(?,?,?,?)",
				group.getGroupId(), group.getGroupName(), group.getGroupIntroduction(), group.getGroupImgPath());
		return i;
	}

	@Override
	public int countGroupInfo(String groupName) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		if (StringUtils.isEmpty(groupName)) {
			Long count = qr.query("select count(*) from tbl_group",new ScalarHandler<Long>(1));
			return count!=null ? (int)count.longValue() : 0;
		}else {
			Long count = qr.query("select count(*) from tbl_group where group_name like ?",
					new ScalarHandler<>(1),"%"+groupName+"%");
			return count!=null ? (int)count.longValue() : 0;
		}
	}

	@Override
	public List<Group> selectGroupsByName(String groupName, int currPage, int pageSize) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		if (StringUtils.isEmpty(groupName)) {
			return qr.query("select group_id, group_name, group_introduction, group_img_path from tbl_group limit ?,?",
					new BeanListHandler<>(Group.class, processor),(currPage-1)*pageSize, pageSize);
		}else {
			return qr.query("select group_id, group_name, group_introduction, group_img_path from tbl_group where group_name like ? limit ?,?",
					new BeanListHandler<>(Group.class, processor),"%"+groupName+"%",(currPage-1)*pageSize, pageSize);
		}
	}

	@Override
	public boolean deleteGroupById(String groupId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		int i = qr.update("delete from tbl_group where group_id=?", groupId);
		if (i > 0) {
			return true;
		}else {
			return false;
		}
	}

	@Override
	public String selectGroupImgPathById(String groupId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		return qr.query("select group_img_path from tbl_group where group_id=?", new ScalarHandler<String>(1), groupId);
	}

	@Override
	public List<Group> selectAllGroups() throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		return qr.query("select group_id, group_name, group_introduction, group_img_path from tbl_group", 
				new BeanListHandler<>(Group.class, processor));
	}

	@Override
	public int insertSignUpInfo(SignUp signUpBean) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		return qr.update("insert into tbl_signup(id,name,gender,classes,telephone,group_name,introduction) values(?,?,?,?,?,?,?)",
				signUpBean.getId(), signUpBean.getName(), signUpBean.getGender(), signUpBean.getClasses(), signUpBean.getTelephone(),
				signUpBean.getGroupName(), signUpBean.getIntroduction());
	}

	@Override
	public List<String> selectGroupsName() throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		return qr.query("select group_name from tbl_group", new ColumnListHandler<>());
	}

	@Override
	public int countSignUpInfo(String name, String phone) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		List<Object>list = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder("select count(*) from tbl_signup where 1=1");
		if(!StringUtils.isEmpty(name)) {
			sql.append(" and name like ?");
			list.add("%"+ name.trim() +"%");
		}
		if(!StringUtils.isEmpty(phone)) {
			sql.append(" and telephone like ?");
			list.add(phone.trim() +"%");
		}
		Long count = qr.query(sql.toString(), new ScalarHandler<>(1), list.toArray());
		return count != null ? (int)count.longValue() : 0;
	}

	@Override
	public List<SignUp> selectSignUp(String name, String phone, int currPage, int pageSize) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		List<Object>list = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder("select id,name,gender,classes,telephone,group_name,introduction from tbl_signup where 1=1");
		if(!StringUtils.isEmpty(name)) {
			sql.append(" and name like ?");
			list.add("%"+name.trim()+"%");
		}
		if(!StringUtils.isEmpty(phone)) {
			sql.append(" and telephone like ?");
			list.add(phone.trim()+"%");
		}
		sql.append(" limit ?,?");
		list.add((currPage-1)*pageSize);
		list.add(pageSize);
		return qr.query(sql.toString(), new BeanListHandler<SignUp>(SignUp.class, processor), list.toArray());
	}

	@Override
	public boolean deleteSignUpById(String id) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		int res = qr.update("delete from tbl_signup where id = ?", id);
		if(res>0) {
			return true;
		}else {
			return false;
		}
	}

	@Override
	public List<SignUp> selectAllSignUp() throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		return qr.query("select id,name,gender,classes,telephone,group_name,introduction from tbl_signup",
				new BeanListHandler<SignUp>(SignUp.class, processor));
	}

	@Override
	public int insertHonorInfo(Honor honor) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		return qr.update("insert into tbl_honor(honor_id, honor_name, honor_introduction, honor_get_date, honor_img_path) values(?,?,?,?,?)", 
				honor.getHonorId(), honor.getHonorName(), honor.getHonorIntroduction(), honor.getHonorGetDate(), honor.getHonorImgPath());
	}

	@Override
	public List<Honor> selectHonor(String name, String date, int currPage, int pageSize) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		List<Object>list = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder("select honor_id, honor_name, honor_introduction, honor_get_date, honor_img_path from tbl_honor where 1=1");
		if(!StringUtils.isEmpty(name)) {
			sql.append(" and honor_name like ?");
			list.add("%"+name.trim()+"%");
		}
		if(!StringUtils.isEmpty(date)) {
			sql.append(" and honor_get_date like ?");
			list.add(date.trim()+"%");
		}
		sql.append(" limit ?,?");
		list.add((currPage-1)*pageSize);
		list.add(pageSize);
		return qr.query(sql.toString(), new BeanListHandler<Honor>(Honor.class, processor), list.toArray());
	}

	@Override
	public int countHonorInfo(String name, String date) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		List<Object>list = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder("select count(*) from tbl_honor where 1=1");
		if(!StringUtils.isEmpty(name)) {
			sql.append(" and honor_name like ?");
			list.add("%"+ name.trim() +"%");
		}
		if(!StringUtils.isEmpty(date)) {
			sql.append(" and honor_get_date like ?");
			list.add(date.trim() +"%");
		}
		Long count = qr.query(sql.toString(), new ScalarHandler<>(1), list.toArray());
		return count != null ? (int)count.longValue() : 0;
	}

	@Override
	public boolean deleteHonorById(String honorId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		int res = qr.update("delete from tbl_honor where honor_id = ?", honorId);
		if(res>0) {
			return true;
		}else {
			return false;
		}
	}
	

	@Override
	public String selectHonorImgPathById(String honorId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		return qr.query("select honor_img_path from tbl_honor where honor_id=?", new ScalarHandler<String>(1), honorId);
	}

	@Override
	public boolean deleteSignUpByIds(String[] ids) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		Object[][] params = new Object[ids.length][];
		for(int i=0;i<ids.length;i++) {
			params[i] = new Object[] {ids[i]};
		}
		int[] s = qr.batch("delete from tbl_signup where id=?", params);
		if(s.length==ids.length) {
			return true;
		}else {
			return false;
		}
	}

	@Override
	public SignUp selectSignUpById(String id) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select id,name,gender,classes,telephone,group_name,introduction from tbl_signup where id = ?";
		return qr.query(sql, new BeanHandler<SignUp>(SignUp.class, processor), id);
	}

	@Override
	public List<Honor> selectAllHonor() throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select honor_id, honor_name, honor_introduction, honor_get_date, honor_img_path from tbl_honor";
		return qr.query(sql, new BeanListHandler<>(Honor.class, processor));
	}

	@Override
	public Admin selectAdmin(String name, String password) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select admin_id,admin_name,admin_face_path from tbl_admin where admin_name=? and admin_password=?";
		return qr.query(sql, new BeanHandler<Admin>(Admin.class, processor), name, password);
	}

	@Override
	public int insertEventInfo(Event event) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "insert into tbl_event(event_date,event_title,event_introduction,event_img_path) values(?,?,?,?)";
		return qr.update(sql, event.getEventDate(),event.getEventTitle(),event.getEventIntroduction(),event.getEventImgPath());
	}

	@Override
	public int insertIntroductionInfo(Introduction intro) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "insert into tbl_introduction(introduction_title,introduction_content,introduction_img_path) values(?,?,?)";
		return qr.update(sql, intro.getIntroductionTitle(), intro.getIntroductionContent(), intro.getIntroductionImgPath());
	}

	@Override
	public List<Introduction> selectAllIntroduction() throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select introduction_id,introduction_title,introduction_content,introduction_img_path from tbl_introduction";
		return qr.query(sql, new BeanListHandler<Introduction>(Introduction.class, processor));
	}

	@Override
	public boolean deleteIntroById(String id) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		int res = qr.update("delete from tbl_introduction where introduction_id = ?", id);
		if(res>0) {
			return true;
		}else {
			return false;
		}
	}
	
	@Override
	public String selectIntroImgPathById(String introId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		return qr.query("select introduction_img_path from tbl_introduction where introduction_id=?", new ScalarHandler<String>(1), introId);
	}

	@Override
	public List<Event> selectAllEvent() throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		return qr.query("select event_id,event_date,event_title,event_introduction,event_img_path", new BeanListHandler<>(Event.class, processor));
	}

	@Override
	public String selectEventImgPathById(String eventId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		return qr.query("select event_img_path from tbl_event where event_id=?", new ScalarHandler<String>(1), eventId);
	}

	@Override
	public boolean deleteEventById(String eventId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		int res = qr.update("delete from tbl_event where event_id = ?", eventId);
		if(res>0) {
			return true;
		}else {
			return false;
		}
	}

	@Override
	public int countEventInfo(String title, String date) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		List<Object>list = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder("select count(*) from tbl_event where 1=1");
		if(StringUtils.isNotEmpty(title)) {
			sql.append(" and event_title like ?");
			list.add("%"+ title.trim() +"%");
		}
		if(StringUtils.isNotEmpty(date)) {
			sql.append(" and event_date like ?");
			list.add(date.trim() +"%");
		}
		Long count = qr.query(sql.toString(), new ScalarHandler<>(1), list.toArray());
		return count != null ? (int)count.longValue() : 0;
	}

	@Override
	public List<Event> selectEvent(String title, String date, int currPage, int pageSize) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		List<Object>list = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder("select event_id, event_title, event_introduction, event_date, event_img_path from tbl_event where 1=1");
		if(StringUtils.isNotEmpty(title)) {
			sql.append(" and event_title like ?");
			list.add("%"+title.trim()+"%");
		}
		if(StringUtils.isNotEmpty(date)) {
			sql.append(" and event_date like ?");
			list.add(date.trim()+"%");
		}
		sql.append(" limit ?,?");
		list.add((currPage-1)*pageSize);
		list.add(pageSize);
		return qr.query(sql.toString(), new BeanListHandler<Event>(Event.class, processor), list.toArray());
	}

}
